package cn.edu.hqu.cst.activity5;
import android.app.Activity;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.CursorAdapter;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;

public class SQL extends Activity
{   SQLiteDatabase db;
    Button bn = null;
    ListView listView;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sql);
        //创建或打开数据库
        //如果my.db3存在则打开该数据库，如果不存在先创建my.db3文件再打开
        /*
 	      SQLite数据库只是一个文件
  	               从本质上来看，SQLite的数据库操作方式是对文件的操作。
 	    */
        db = SQLiteDatabase.openOrCreateDatabase(this.getFilesDir().toString() + "my.db3", null);
        listView = (ListView) findViewById(R.id.show);
        bn = (Button) findViewById(R.id.ok);
        bn.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View source) {
                //获取用户输入
                EditText titleEdit = (EditText) findViewById(R.id.title);
                EditText contentEdit = (EditText) findViewById(R.id.content);
                String title = titleEdit.getText().toString();
                String content = contentEdit.getText().toString();

                try {
                    insertData(db, title, content);

                    Cursor cursor = db.rawQuery("select * from news_inf", null);
                    inflateList(cursor);
                } catch (SQLException se) {
                    //执行DDL创建数据表
                    db.execSQL("create table news_inf(_id integer" +
                            " primary key autoincrement," +
                            " news_title varchar(50)," +
                            " news_content varchar(255))");
                    //执行insert语句插入数据
                    insertData(db, title, content);
                    //执行查询
                    Cursor cursor = db.rawQuery("select * from news_inf", null);
                    inflateList(cursor);
                }
            }
        });
    }

    private void insertData(SQLiteDatabase db, String title, String content){
        //执行插入语句
        db.execSQL("insert into news_inf values(null, ?, ?)", new String[]{title, content});
    }

    private void inflateList(Cursor cursor){
        //填充SimpleCursorAdapter
        SimpleCursorAdapter adapter = new SimpleCursorAdapter(SQL.this,
                R.layout.line, cursor, new String[]{"news_title", "news_content"},
                new int[]{R.id.my_title, R.id.my_content},
                CursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER);
        listView.setAdapter(adapter);
    }

    @Override
    protected void onDestroy() {
        super.onDestroy();
        //退出程序时关闭SQLiteDatabase
        if(db != null && db.isOpen()){
            db.close();
        }
    }
}

